﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DA
{
    public class TheoDoiCongNhanDA:BaseConnection
    {
        public SqlDataAdapter theoDoiCongNhanAdapter { set; get; }

        public TheoDoiCongNhanDA()
        {
           
        }

        public int themCongNhan(string maCongNhan, string maDonHang, string maSanPham)
        {
            String sqlStm = "INSERT INTO TheoDoiCongNhan(MaCongNhan, MaDonHang, MaSanPham) VALUES(@maCongNhan, @maDonHang, @maSanPham )";
            openConnect();
            SqlCommand cmd = new SqlCommand(sqlStm, con);
            cmd.Parameters.Add("@maCongNhan", SqlDbType.VarChar).Value = maCongNhan;
            cmd.Parameters.Add("@maDonHang", SqlDbType.NVarChar).Value = maDonHang;
            cmd.Parameters.Add("@maSanPham", SqlDbType.NVarChar).Value = maSanPham;
            return cmd.ExecuteNonQuery();
        }

        public DataSet layDanhSachTheoDoiCongNhan(string maDonHang)
        {
            String sqlStm = "SELECT DISTINCT tdcn.MaCongNhan, MaSanPham, cn.TenCongNhan ";
            sqlStm += "FROM TheoDoiCongNhan tdcn ";
            sqlStm += "INNER JOIN CongNhan cn ON (tdcn.MaCongNhan = cn.MaCongNhan) ";
            sqlStm += "WHERE MaDonHang ='" + maDonHang + "'";
            return layDuLieuTheoCauLenhSQL(sqlStm);
        }
        public DataSet loiCongNhanTheoThang(string thang, string nam)
        {
            DataSet ds = new DataSet();

            String sqlStm = " SELECT * FROM";
                sqlStm += " (SELECT ";
                sqlStm += " cn.MaCongNhan, ";
                sqlStm += " cn.TenCongNhan, ";
                sqlStm += " cn.BacCongNhan, ";
                sqlStm += " SUM(tdcn.SoLoi) OVER (PARTITION BY tdcn.MaCongNhan) AS SoLuongLoi,";
                sqlStm += " 100*(ROUND(CAST((SUM(tdcn.PhePham) OVER (PARTITION BY tdcn.MaCongNhan)) AS FLOAT)/ CAST((SELECT DISTINCT SUM(tdcn.SanLuong) OVER (PARTITION BY tdcn.MaCongNhan)) AS float), 4)) AS PhanTramLoi ";
                sqlStm += " FROM CongNhan cn";
                sqlStm += " INNER JOIN TheoDoiCongNhan tdcn ON (cn.MaCongNhan = tdcn.MaCongNhan)";
                sqlStm += " WHERE ";
                sqlStm += " datepart(month, tdcn.NgayTheoDoi) = '" + thang + "' AND datepart(year, tdcn.NgayTheoDoi) = '" + nam + "'";
                sqlStm += " OR";
                sqlStm += " datepart(month, tdcn.NgayKiemTra) = '" + thang + "' AND datepart(year, tdcn.NgayKiemTra) = '" + nam + "'";
                sqlStm += " )tmp";
                sqlStm += " WHERE tmp.PhanTramLoi is not null";           

            openConnect();

            SqlCommand cmd = new SqlCommand(sqlStm, con);
            theoDoiCongNhanAdapter = new SqlDataAdapter(cmd);
            theoDoiCongNhanAdapter.SelectCommand = cmd;
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(theoDoiCongNhanAdapter);
            theoDoiCongNhanAdapter.Fill(ds, "TheoDoiCongNhan");
            closeConnect();
            return ds;  
                       
            //return layDuLieuTheoCauLenhSQL(sqlStm);
        }
        
        public DataPacket layDanhSachTheoDoiCongNhanDungDeInsert()
        {
            String sqlStm = "SELECT MaDonHang, MaSanPham, MaCongNhan, SanLuong, PhePham, NgayTheoDoi";
            sqlStm += " FROM TheoDoiCongNhan";
            sqlStm += " WHERE 1 = 0";
            return layDuLieuBang(sqlStm);
        }

        public DataPacket layDanhSachTheoDoiCongNhanDeThem()
        {
            String sqlStm = "SELECT MaDonHang, MaSanPham, MaCongNhan";
            sqlStm += " FROM TheoDoiCongNhan";
            sqlStm += " WHERE 1 = 0";
            return layDuLieuBang(sqlStm);
        }

        public bool capNhatDanhSachTheoDoiCongNhan(string maDonHang, string maCongNhan, string maSanPham, string sanLuong, string phePham, string ngayTheoDoi)
        {
            openConnect();

            String sqlStm = "UPDATE TheoDoiCongNhan ";
            sqlStm += " SET SanLuong = " + sanLuong + ", ";
            sqlStm += " PhePham = " + phePham + ", ";
            //sqlStm += " AA = " + sanLuong + - + phePham + ", ";
            sqlStm += " NgayTheoDoi = '" + ngayTheoDoi + "'";
            sqlStm += " WHERE MaDonHang = '" + maDonHang + "'";
            sqlStm += " AND MaSanPham = '" + maSanPham + "'";
            sqlStm += " AND MaCongNhan = '" + maCongNhan + "'";
            //sqlStm += " AND MaLoi = '" + maLoi + "'";

            SqlCommand cmd = new SqlCommand(sqlStm, con);
            int result = cmd.ExecuteNonQuery();
            return result==1?true:false;
        }

        public DataSet layDanhSachTheoDoiCongNhan(String ngayTheoDoi, String maDonHang)
        {
            String stmSql = "SELECT DISTINCT ";
            stmSql += "tdcn.MaSanPham, ";
            stmSql += "cn.MaCongNhan, ";
            stmSql += "tdcn.SanLuong, ";
            stmSql += "tdcn.PhePham ";
            stmSql += "FROM TheoDoiCongNhan tdcn ";
            stmSql += "INNER JOIN CongNhan cn ON (cn.MaCongNhan = tdcn.MaCongNhan) ";
            stmSql += "INNER JOIN ThongTinSanPham ttsp ON (ttsp.MaSanPham = tdcn.MaSanPham) ";
            stmSql += "WHERE tdcn.NgayTheoDoi = '" + ngayTheoDoi + "' ";
            stmSql += "AND tdcn.MaDonHang = '" + maDonHang + "' ";
            return layDuLieuTheoCauLenhSQL(stmSql);
        }

        public DataSet loiCongNhanTheoDonHang(string maDonHang)
        {
            DataSet ds = new DataSet();


            String sqlStm = "  SELECT DISTINCT dh.MaDonHang, tdcn.MaCongNhan, tdcn.MaSanPham, "  ;

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 1 ";
            sqlStm += "  )  AS CHOP, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 2 ";
            sqlStm += "  )  AS VETRO, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 3 ";
            sqlStm += "  )  AS LEMMAU, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 4 ";
            sqlStm += "  )  AS HOAVAN, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 5 ";
            sqlStm += "  )  AS MECANHMEN, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 6 ";
            sqlStm += "  )  AS BEGOCMEN, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 7 ";
            sqlStm += "  )  AS BELOPDE, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 8 ";
            sqlStm += "  )  AS PHUMEN, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 9 ";
            sqlStm += "  )  AS QUANVETDAU, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 10 ";
            sqlStm += "  )  AS CHAYVANH, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 11 ";
            sqlStm += "  )  AS LODAMI, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 12 ";
            sqlStm += "  )  AS NUTXE, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 13 ";
            sqlStm += "  )  AS KHONGCOLOGO, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 14 ";
            sqlStm += "  )  AS MAUKHONGTAN, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 15 ";
            sqlStm += "  )  AS BAMAU, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2 ";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan ";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang ";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham ";
            sqlStm += "  AND tdcn2.MaLoi = 16 ";
            sqlStm += "  )  AS KHAC, ";

            sqlStm += "  (SELECT SUM(CAST(SoLoi AS INT)) FROM TheoDoiCongNhan tdcn2";
            sqlStm += "  WHERE tdcn2.MaCongNhan = tdcn.MaCongNhan";
            sqlStm += "  AND tdcn2.MaDonHang = tdcn.MaDonHang";
            sqlStm += "  AND tdcn2.MaSanPham = tdcn.MaSanPham";
            sqlStm += "  )  AS TONG";

            sqlStm += "  FROM DonHang dh"  ;
            sqlStm += "  INNER JOIN TheoDoiCongNhan tdcn ON (tdcn.MaDonHang = '" + maDonHang + "' AND dh.MaSanPham = tdcn.MaSanPham)";
            sqlStm += " WHERE dh.MaDonHang = '" + maDonHang + "'";
            openConnect();

            SqlCommand cmd = new SqlCommand(sqlStm, con);
            theoDoiCongNhanAdapter = new SqlDataAdapter(cmd);
            theoDoiCongNhanAdapter.SelectCommand = cmd;
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(theoDoiCongNhanAdapter);
            theoDoiCongNhanAdapter.Fill(ds, "TheoDoiCongNhan");
            closeConnect();
            return ds;  
        }

        public int capNhatLoiCongNhan(String maDonHang, String maSanPham, String maCongNhan, String maLoi, Int32 soLoi, String ngaySanXuat, String ngayKiemTra)
        {
            //EXEC sp_capNhatBangTheoDoiCongNhanTheoLoi '1', 'L-18', '1', '5', 1, '2013/12/12', '2012/12/12'
            
                List<StoreProcedureParameter> lstParameter = new List<StoreProcedureParameter>();
                lstParameter.Add(new StoreProcedureParameter("@maDonHang"      ,maDonHang 	    ,SqlDbType.NVarChar));
                lstParameter.Add(new StoreProcedureParameter("@maSanPham"   ,maSanPham 	    ,SqlDbType.NVarChar));
                lstParameter.Add(new StoreProcedureParameter("@maCongNhan"  ,maCongNhan 	,SqlDbType.NVarChar));
                lstParameter.Add(new StoreProcedureParameter("@maLoi"       ,maLoi 			,SqlDbType.NVarChar));
                lstParameter.Add(new StoreProcedureParameter("@soLoi"       ,soLoi+""		,SqlDbType.Int));
                lstParameter.Add(new StoreProcedureParameter("@NgaySanXuat" ,ngaySanXuat	,SqlDbType.DateTime));
                lstParameter.Add(new StoreProcedureParameter("@NgayKiemTra" ,ngayKiemTra	,SqlDbType.DateTime));


               return callSPExecute("sp_capNhatBangTheoDoiCongNhanTheoLoi", lstParameter);
          
        }
    }
}
